from fastapi import APIRouter
from ..model.master import Lot, PanelImage, Failure, FailureImage, Material, AOI
from ..model.slave import AI, JudgmentFailure
from ..model.mes import simulatorresult
from sqlalchemy import and_, or_
import json
import math
from sqlalchemy import func
from ..utils.database import SessionLocal, MES_Session
from ..utils.post_item import AOIImageItem, PanelItem, FailureJudgeItem, AOILotDetailItem, FailureImageItem, PanelImageItem, PanelParetoItem, LotRenameItem, AOILotUniqItem, PanelHistoryItem, UpdateMESItem, JudementItem
from decimal import Decimal
import os
from datetime import timedelta
from ..config import Config, csv_dir
import time
from ..utils.lot_job import failure_image_task, panel_pareto_task, panel_box_heatmap_task, panel_pixel_heatmap_task, export_csv_task
import time
import numpy as np
import cv2
from .. import loop
from ..utils.lot import get_material_lots, get_material_name, get_aoi_name, panel_failure_data, lot_filter, panel_filter, define_error, upload_mes_task, get_lots_job
import csv
import io
from fastapi.responses import StreamingResponse, FileResponse
import uuid
from ..utils.task_queue import enqueue_task


router = APIRouter()

root_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


# 结果详情，按 lot 显示
@router.post('/lot-detail')
async def lot_detail(item: AOILotDetailItem):
    page_index = item.page_index
    page_size = item.page_size
    lot_name = item.lot_name
    end_time = item.end_time 
    start_time = item.start_time
    material_names = item.material_names
    server_hosts = item.server_hosts

    res = {}
    offset_data = page_size * (page_index-1)

    with SessionLocal() as session:

        lots = lot_filter(session, lot_name=lot_name, start_time=start_time, end_time=end_time, material_names=material_names, server_hosts=server_hosts)

        total_num = len(lots.all())

        lot_paginate = lots.order_by(Lot.start_time.desc()).offset(offset_data).limit(page_size).all()

        lot_data = []

        for lot in lot_paginate:
            lot_item = {}
            lot_item["lot_name"] = lot.lot_name
            lot_item["lot_code"] = lot.lot_code
            lot_item["status"] = lot.status
            lot_item["material_name"] = get_material_name(lot.material_code)
            lot_item["aoi_name"] = get_aoi_name(lot.aoi_code) if lot.aoi_code else ""
            lot_item["start_time"] = lot.start_time.strftime('%Y-%m-%d %H:%M:%S')
            lot_item["end_time"] = lot.end_time.strftime('%Y-%m-%d %H:%M:%S') if lot.end_time else ""
            lot_item["server_host"] = lot.server_host
            ai = session.query(AI).filter(AI.hostname==lot.server_host).first()
            lot_item["ai_name"] = ai.ai_name
            lot_item["comment"] = lot.comment

            panels = session.query(PanelImage).filter(PanelImage.lot_code == lot.lot_code)

            panel_num = session.query(func.count(PanelImage.id)).filter(PanelImage.lot_code == lot.lot_code).scalar()
            
            aoi_accuracy = 0
            ai_accuracy = 0

            ai_gray = 0
            ai_ng = 0

            judgment_same = 0
            judgment_diff = 0
            judgment_no = 0
            judgment_other = 0

            judgement_accuracy = 0

            ai_missdetect = 0
            ai_overdetect = 0
            ai_overdetect_ng = 0
            ai_overdetect_gray = 0

            for panel in panels:
                aoi_result = panel.aoi_result
                ai_result = panel.ai_result
                judgment = panel.judgment

                # 如果 aoi 检测是正确的图片
                if aoi_result == 1:
                    aoi_accuracy += 1

                # 如果 ai 检测是正确的图片
                if ai_result == 1:
                    ai_accuracy += 1
                elif ai_result == 3:
                    ai_gray += 1
                else:
                    ai_ng += 1


                # 判断人工复检结果
                if ai_result == judgment:
                    judgment_same += 1
                else:
                    # 未复判
                    if judgment == 0:
                        judgment_no += 1
                    # 其它情况
                    elif judgment == 3:
                        judgment_other += 1
                    # 人工复判：OK, ai: Gray, NG
                    elif judgment == 1:
                        # ai 过检
                        ai_overdetect += 1
                        judgment_diff += 1
                        # ai: Gray
                        if ai_result == 3:
                            ai_overdetect_gray += 1
                        else:
                            ai_overdetect_ng += 1
                    # 人工复判：NG, ai: Gray, OK
                    else:
                        # ai: Gray
                        if ai_result == 3:
                            judgment_same += 1
                        # ai: OK
                        else:
                            # ai 漏检
                            ai_missdetect += 1
                            judgment_diff += 1

                if judgment == 1:
                    judgement_accuracy += 1

            lot_item["panel_num"] = panel_num

            if panel_num == 0:
                lot_data.append(lot_item)
                continue

            lot_item["aoi_accuracy_rate"] = int(Decimal(aoi_accuracy/panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)
            lot_item["aoi_accuracy"] = aoi_accuracy
            lot_item["aoi_error"] = panel_num - aoi_accuracy

            # ai 良品率
            lot_item["ai_accuracy_rate"] = int(Decimal(ai_accuracy/panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)
            # ai 良品数量
            lot_item["ai_accuracy"] = ai_accuracy

            # ai 不良数量
            lot_item["ai_error"] = panel_num - ai_accuracy
            lot_item["ai_gray"] = ai_gray
            lot_item["ai_ng"] = ai_ng

            lot_item["judgment_same"] = judgment_same
            lot_item["judgment_diff"] = judgment_diff
            lot_item["judgment_no"] = judgment_no
            lot_item["judgment_other"] = judgment_other

            lot_item["judgement_accuracy"] = int(Decimal(judgement_accuracy/panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)

            # ai 过检率
            lot_item["ai_overdetect_rate"] = Decimal(ai_overdetect/panel_num).quantize(Decimal("0.0001"), rounding="ROUND_HALF_UP")*100
            lot_item["ai_overdetect"] = ai_overdetect
            lot_item["ai_overdetect_gray"] = ai_overdetect_gray
            lot_item["ai_overdetect_gray_rate"] = Decimal(ai_overdetect_gray/panel_num).quantize(Decimal("0.0001"), rounding="ROUND_HALF_UP")*100
            lot_item["ai_overdetect_ng"] = ai_overdetect_ng
            lot_item["ai_overdetect_ng_rate"] = Decimal(ai_overdetect_ng/panel_num).quantize(Decimal("0.0001"), rounding="ROUND_HALF_UP")*100

            lot_item["ai_missdetect_rate"] = Decimal(ai_missdetect/panel_num).quantize(Decimal("0.0001"), rounding="ROUND_HALF_UP")*100
            lot_item["ai_missdetect"] = ai_missdetect

            lot_data.append(lot_item)

        res["lot"] = lot_data

        res["total_num"] = total_num

        return res


@router.get('/failure-type')
async def failure_type():
    all_failure_type = {}
    with SessionLocal() as session:
        failures = session.query(Failure).filter_by(del_flag="0")
        for failure in failures:
            all_failure_type[failure.failure_id] = [failure.failure_name, failure.failure_type]
        return {"failure_types": all_failure_type}


@router.post('/panel-pareto')
async def panel_pareto(item: PanelParetoItem):
    end_time = item.end_time
    start_time = item.start_time
    material_names = item.material_names
    server_hosts = item.server_hosts

    res = await loop.run_in_executor(None, panel_pareto_task, start_time, end_time, material_names, server_hosts)

    return {"predict_data": res}


# 结果详情 -> 按 panel 显示
@router.post('/panel-image')
async def panel_image(item: FailureImageItem):
    page_index = item.page_index
    page_size = item.page_size
    lot_name = item.lot_name
    lot_code = item.lot_code
    panel_ids = item.panel_ids
    failure_ids = item.failure_ids
    # 综合判定结果
    judgments = item.judgments if item.judgments else []
    end_time = item.end_time
    start_time = item.start_time
    ai_result = item.ai_result
    aoi_result = item.aoi_result
    pjudgment = item.pjudgment
    server_hosts = item.server_hosts

    # aoi生成的 panel 时间
    aoi_start_time = item.aoi_start_time
    aoi_end_time = item.aoi_end_time

    with SessionLocal() as session:

        panel_images = panel_filter(session, lot_name=lot_name, lot_code=lot_code, panel_ids=panel_ids, failure_ids=failure_ids, judgments=judgments, pjudgment=pjudgment, end_time=end_time, start_time=start_time, ai_result=ai_result, aoi_start_time=aoi_start_time, aoi_end_time=aoi_end_time, aoi_result=aoi_result, server_hosts=server_hosts)

        if page_size:
            offset_data = page_size * (page_index-1)
            panel_image_paginate = panel_images.order_by(PanelImage.aoi_time.desc()).offset(offset_data).limit(page_size).all()
            total_page = math.ceil(len(panel_images.all()) / page_size)
        else:
            # 如果不传 page_size，返回所有的 panel
            panel_image_paginate = panel_images.order_by(PanelImage.aoi_time.desc()).all()
            total_page = 1
        total_num = len(panel_images.all())

        predict_data = panel_failure_data(panel_image_paginate, session)

        return {"predict_data": predict_data, "total_page": total_page, "total_num": total_num}


# 结果详情 -> 按缺陷图显示
@router.post('/failure-image')
async def failure_image(item: FailureImageItem):
    page_index = item.page_index
    page_size = item.page_size
    lot_name = item.lot_name
    lot_code = item.lot_code
    panel_id = item.panel_id
    panel_code = item.panel_code
    failure_ids = item.failure_ids
    judgments = item.judgments if item.judgments else []
    end_time = item.end_time
    start_time = item.start_time
    server_hosts = item.server_hosts

    res = await loop.run_in_executor(None, failure_image_task, page_index, page_size, lot_name, lot_code, panel_id, panel_code, failure_ids, judgments, end_time, start_time, server_hosts)

    return res


# 通过一个检测点图片，获取该监测点图片所在的 panel 图片上的所有检测点信息
@router.post('/image-detail')
async def image_detail(item: AOIImageItem):
    image_code = item.image_code

    with SessionLocal() as session:

        image = session.query(FailureImage).filter_by(image_code=image_code).first()
        panel_code = image.panel_code

        panel_image = session.query(PanelImage).filter_by(panel_code=panel_code).first()

        item = {}
        item["failure_id"] = image.failure_id
        item["file_name"] = image.file_name
        item["file_path"] = image.file_path
        item["detection_info"] = json.loads(panel_image.detection_info) if panel_image.detection_info else []
        item["parent_file_path"] = panel_image.file_path
        item["thumb_file_path"] = panel_image.thumb_file_path
        item["aoi_file_path"] = panel_image.aoi_file_path
        item["panel_id"] = panel_image.panel_id

        return item
    

# 人工复检-通过输入 panel id，获取该panel 图片上的所有检测点信息
@router.post('/panel-detail')
async def panel_detail(item: PanelItem):
    panel_id = item.panel_id
    lot_name = item.lot_name
    # 获取重复数据的第几条
    index = item.index
    material_name = item.material_name

    res = {}

    # 先检查 MES 表是否是 PH01
    with MES_Session() as mes_session:
        panel_ph01= mes_session.query(simulatorresult).filter(simulatorresult.ChipID==panel_id).first()
        if panel_ph01:
            res = {"pho1": panel_ph01.RetestCode}    

    with SessionLocal() as session:
        if lot_name:
            lots = session.query(Lot.lot_code).filter(Lot.lot_name == lot_name).order_by(Lot.id.desc()).all()

            lot_codes = [row[0] for row in lots]

            panel_images = session.query(PanelImage).filter(and_(PanelImage.panel_id == panel_id, PanelImage.lot_code.in_(lot_codes))).order_by(PanelImage.aoi_time.desc())
        else:
            panel_images = session.query(PanelImage).filter(PanelImage.panel_id == panel_id).order_by(PanelImage.aoi_time.desc())

        if material_name:
            materials = session.query(Material).filter(Material.material_name==material_name).all()
            
            material_codes = [material.material_code for material in materials]

            panel_images = panel_images.filter(PanelImage.material_code.in_(material_codes))

        panel_images = panel_images.all()

        if len(panel_images) == 0:
            res.update({"code": 0, "message": "未检测出"})
            return res
        elif len(panel_images) == 1:
            res.update({"code": 1, "mesage": "只检出一个", "total_num": 1})
        else:
            res.update({"code": 2, "message": "检查出重复 panel", "total_num": len(panel_images)})

        panel_image = panel_images[index]
        
        item = {}

        lot_code = panel_image.lot_code

        lot = session.query(Lot).filter_by(lot_code=lot_code).first()

        lot_name = lot.lot_name

        item["detection_info"] = json.loads(panel_image.detection_info) if panel_image.detection_info else []
        item["thumb_file_path"] = panel_image.thumb_file_path
        item["aoi_file_path"] = panel_image.aoi_file_path
        item["judgment"] = panel_image.judgment
        item["panel_id"] = panel_id
        item["panel_code"] = panel_image.panel_code
        item["lot_name"] = lot_name
        item["server_host"] = panel_image.server_host
        item["ai_result"] = panel_image.ai_result
        item["aoi_result"] = panel_image.aoi_result
        item["aoi_failures"] = json.loads(panel_image.aoi_failures) if panel_image.aoi_failures else []
        item["img_roi"] = json.loads(panel_image.img_roi) if panel_image.img_roi else []

        res['panel']= item
        return res
    

# 人工复检-点击确定复判结果
@router.post('/panel-judge')
async def panel_judge(item: FailureJudgeItem):
    failure_code = item.failure_code

    with SessionLocal() as session:

        judgment_failure = session.query(JudgmentFailure).filter_by(failure_code=failure_code).first()

        if not judgment_failure:
            item = JudgmentFailure(failure_code=failure_code)
            session.add(item)

            session.commit()

            return {"code": 0, "message": "添加新的 judegment failure 成功"}
        else:
            return {"code": 0, "message": "无需添加"}


# 返回所有物料
@router.get('/material')
async def material():
    all_material = []
    with SessionLocal() as session:
        materials = session.query(Material).filter_by(del_flag="0")
        for material in materials:
            item = {}
            item["material_name"] = material.material_name
            item["material_code"] = material.material_code
            item["background_image"] = material.background_image
            item["server_host"] = material.server_host
            all_material.append(item)
        return {"materials": all_material}
    

@router.post('/lot-detail-by-day')
async def lot_detail_by_day(item: PanelImageItem):
    end_time = item.end_time 
    start_time = item.start_time
    material_names = item.material_names
    server_hosts = item.server_hosts

    res = {}

    with SessionLocal() as session:
        lots = session.query(Lot).filter(and_(Lot.del_flag=='0'))

        # 前端选择的时间和数据库的时间会相差 8 个时区
        if start_time:

            # 按天拆分时间段
            time_list = {}

            current_time = start_time

            while current_time <= end_time:
                current_end_time = current_time + timedelta(days=1)
                current_end_time = current_end_time.replace(hour=0, minute=0, second=0, microsecond=0)

                current_date = current_time.strftime('%Y-%m-%d')

                if current_end_time<=end_time:
                    time_list[current_date] = [current_time, current_end_time]

                else:
                    time_list[current_date] = [current_time, end_time]

                current_time = current_end_time

            lots = lots.filter(Lot.start_time >= start_time).filter(Lot.end_time <= end_time, Lot.end_time.isnot(None))

            # 对 start_time 进行按日期分组, 同一天的会放在一组
            all_day_time = session.query(func.date_format(Lot.start_time, '%Y-%m-%d').label('date')).filter(Lot.start_time.between(start_time, end_time)).group_by('date').all()
        else:
            all_day_time = session.query(func.date_format(Lot.start_time, '%Y-%m-%d').label('date')).group_by('date').all()

        lot_data = []

        for day_time in all_day_time:
            day_time = day_time[0]

            # 某一天
            if start_time:
                s_time, e_time = time_list[day_time]
                lots = session.query(Lot).filter(Lot.start_time.between(s_time, e_time))
            else:
                lots = session.query(Lot).filter(func.date_format(Lot.start_time, '%Y-%m-%d')==day_time)

            if server_hosts:
                lots = lots.filter(Lot.server_host.in_(server_hosts))

            if material_names:
                lots = get_material_lots(material_names, lots)
                
            all_panel_num = 0
            all_ai_accuracy = 0
            all_aoi_accuracy = 0
            all_judgement_accuracy = 0

            # 统计每天的 item
            lot_item = {}
            for lot in lots.all():

                panels = session.query(PanelImage).filter(PanelImage.lot_code == lot.lot_code)

                panel_num = session.query(func.count(PanelImage.id)).filter(PanelImage.lot_code == lot.lot_code).scalar()

                all_panel_num += panel_num
                
                aoi_accuracy = 0
                ai_accuracy = 0
                judgement_accuracy = 0

                for panel in panels:
                    aoi_result = panel.aoi_result
                    ai_result = panel.ai_result
                    judgment = panel.judgment

                    # 如果 aoi 检测是正确的图片
                    if aoi_result == 1:
                        aoi_accuracy += 1

                    # 如果 ai 检测是正确的图片
                    if ai_result == 1:
                        ai_accuracy += 1

                    if judgment == 1:
                        judgement_accuracy += 1

                all_aoi_accuracy += aoi_accuracy
                all_ai_accuracy += ai_accuracy
                all_judgement_accuracy += judgement_accuracy

            # 物料总数
            lot_item["panel_num"] = all_panel_num

            if all_panel_num == 0:
                continue
            # 同一天的 aoi 良率
            lot_item["aoi_accuracy_rate"] = int(Decimal(all_aoi_accuracy/all_panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)
            # 同一天的 ai 良率
            lot_item["ai_accuracy_rate"] = int(Decimal(all_ai_accuracy/all_panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)
            # 同一天的人工复判良率
            lot_item["judgement_accuracy"] = int(Decimal(all_judgement_accuracy/all_panel_num).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")*100)
            lot_item["day_time"] = day_time

            lot_data.append(lot_item)
        res["lot_per_day"] = lot_data
        return res
    

# 所有 panel 图片上绘制检测点
@router.post('/panel-image-detail')
async def panel_image_detail(item: PanelImageItem):
    material_names = item.material_names
    failure_ids = item.failure_ids
    start_time = item.start_time
    end_time = item.end_time
    page_index = item.page_index
    page_size = item.page_size
    server_hosts = item.server_hosts

    with SessionLocal() as session:

        panel_images = panel_filter(session, material_names=material_names, start_time_contain=start_time, end_time_contain=end_time)
        panel_images = panel_images.filter(PanelImage.aoi_result==2)

        all_panel_images = []

        if failure_ids:
            for panel_image in panel_images.all():
                panel_code = panel_image.panel_code
                failure_images = session.query(FailureImage).filter_by(panel_code=panel_code).filter(FailureImage.failure_id.in_(failure_ids)).all()
                
                if failure_images:
                    item = {}
                    # ok 的图片不展示
                    try:
                        item["detection_info"] = json.loads(panel_image.detection_info) if panel_image.detection_info else []
                    except Exception:
                        continue
                    item["thumb_file_path"] = panel_image.thumb_file_path
                    item["panel_id"] = panel_image.panel_id
                    item["server_host"] = panel_image.server_host
                    all_panel_images.append(item)
        else:
            for panel_image in panel_images:
                item = {}
                # ok 的图片不展示
                try:
                    item["detection_info"] = json.loads(panel_image.detection_info) if panel_image.detection_info else []
                except Exception:
                    continue
                item["thumb_file_path"] = panel_image.thumb_file_path
                item["panel_id"] = panel_image.panel_id
                item["server_host"] = panel_image.server_host
                all_panel_images.append(item)


        if page_size:
            offset_data = page_size * (page_index-1)
            panel_image_paginate = all_panel_images[offset_data: page_size * page_index]
            total_page = math.ceil(len(all_panel_images) / page_size)
        else:
            # 如果不传 page_size，返回所有的 panel
            panel_image_paginate = all_panel_images
            total_page = 1

        total_num = len(all_panel_images)
            
        return {"panel_images": panel_image_paginate, "total_page": total_page, "total_num": total_num}


# 所有 panel 图片上的使用 矩形框 绘制检测点
@router.post('/panel-box-heatmap')
async def panel_box_heatmap(item: PanelImageItem):
    material_name = item.material_name
    failure_ids = item.failure_ids
    start_time = item.start_time
    end_time = item.end_time

    res = await loop.run_in_executor(None, panel_box_heatmap_task, material_name, failure_ids, start_time, end_time)

    return res


# 所有 panel 图片上的使用 矩形框 绘制检测点
@router.post('/panel-pixel-heatmap')
async def panel_pixel_heatmap(item: PanelImageItem):
    material_name = item.material_name
    failure_ids = item.failure_ids
    start_time = item.start_time
    end_time = item.end_time
    server_hosts = item.server_hosts


    res = await loop.run_in_executor(None, panel_pixel_heatmap_task, material_name, failure_ids, start_time, end_time, server_hosts)

    return res


# 所有 panel 图片上使用 像素点 绘制检测点
@router.post('/panel-image-heatmap')
async def panel_image_heatmap(item: PanelImageItem):
    t1 = time.time()
    material_name = item.material_name
    failure_ids = item.failure_ids
    start_time = item.start_time
    end_time = item.end_time

    # 由于图片太大，需要对图片阵进行缩放
    # scale = 1/6
    # scale = 1/32
    scale = 1

    with SessionLocal() as session:

        material = session.query(Material).filter(Material.material_name==material_name).first()

        background_image = material.background_image

        # 读取图像文件
        cv_image = cv2.imread(background_image)
        cv_shape = cv_image.shape

        width = int(cv_shape[1]*scale)
        height = int(cv_shape[0]*scale)

        # 缩放图像
        resized_image = cv2.resize(cv_image, (width, height))

        lots = session.query(Lot.lot_code).filter(Lot.del_flag == '0')

        lots = lots.filter(Lot.material_name==material_name)
            
        if start_time:
            
            time_conditions = []
            time_conditions.append(and_(Lot.start_time <= end_time, Lot.start_time >= start_time))
            time_conditions.append(and_(Lot.end_time <= end_time, Lot.end_time >= start_time, Lot.end_time.isnot(None)))
            time_conditions.append(and_(Lot.end_time >= end_time, Lot.start_time <= start_time, Lot.end_time.isnot(None)))

            if time_conditions:
                lots = session.query(Lot).filter(or_(*time_conditions)).all()

        panel_images = session.query(PanelImage)

        lot_codes = [row[0] for row in lots.all()]
        panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes)).order_by(PanelImage.id.desc()).all()

        heatmap = np.zeros((width, height))

        if failure_ids:
            for panel_image in panel_images:
                panel_code = panel_image.panel_code
                # failure_images = session.query(FailureImage).filter_by(panel_code=panel_code).filter(FailureImage.failure_id.in_(failure_ids)).all()
                failure_image_ids = session.query(FailureImage.failure_id).filter_by(panel_code=panel_code).all()
                failure_image_ids = [failure_image_id[0] for failure_image_id in failure_image_ids]
                failure_images = list(set(failure_image_ids).intersection(failure_ids))
               
                if failure_images:
                    item = {}
                    detection_info = json.loads(panel_image.detection_info) if panel_image.detection_info else []

                    for box_item in detection_info:
                        failure_id = box_item["failure_id"]
                        # 过滤条件有错误类型的时候，只显示指定错误类型的错误
                        if failure_id in failure_ids:
                            box = box_item["box"]
                            x0 = int(box[0]*scale)
                            y0 = int(box[1]*scale)
                            x1 = int(box[2]*scale)
                            y1 = int(box[3]*scale)
                            heatmap[x0:x1, y0:y1] += 1
                    
        else:
            for panel_image in panel_images:
                ttt1 = time.time()
                item = {}
                detection_info = json.loads(panel_image.detection_info) if panel_image.detection_info else []

                for box_item in detection_info:
                    box = box_item["box"]
                    x0 = int(box[0]*scale) - 1
                    y0 = int(box[1]*scale) - 1
                    x1 = int(box[2]*scale) - 1
                    y1 = int(box[3]*scale) - 1
                    heatmap[x0:x1, y0:y1] += 1

        max_value = np.max(heatmap)

        # 只返回又错误的像素点坐标
        # indices = np.where(heatmap > 10) 
        indices = np.where(heatmap > 0) 

        all_heatmap = [[i, j, int(heatmap[i, j])] for i, j in zip(indices[0], indices[1])]   

        return {"resize_heatmap shape: ": heatmap.shape, "all_heatmap len: ": len(all_heatmap), "heatmap": str(all_heatmap)}


@router.get('/conf')
async def get_infer_type():
        config = Config()
        config.read(os.path.join(root_path, 'config', 'server.ini'))
        infer_type = config.getint('lot', 'infer_type')
        ctype = config.getint('clear', 'ctype')
        scp_type = config.getint('lot', 'scp_type')
        return {"infer_type": infer_type, "ctype": ctype,"scp_type": scp_type}


@router.post('/rename')
async def rename_lot(item: LotRenameItem):
    lot_code = item.lot_code
    lot_name = item.lot_name
    comment = item.comment

    with SessionLocal() as session:
        lot = session.query(Lot).filter(Lot.lot_code==lot_code).first()

        lot.lot_name = lot_name
        lot.comment = comment

        session.commit()

    return {"msg": "lot 重命名成功"}

# 人工复检，lot 名称下拉框
@router.post('/lots')
async def get_lots(item: AOILotUniqItem):
    # 从数据库中获取所有的 lot 数据并对 lotname 去重
    # 模糊查询
    lot_name = item.lot_name
    is_complete = item.is_complete

    res = await loop.run_in_executor(None, get_lots_job, lot_name, is_complete)
    
    return res
    

@router.post('/panel-history')
async def panel_image(item: PanelHistoryItem):
    lot_name = item.lot_name
    panel_id = item.panel_id
    material_name = item.material_name

    # 一定要传 panel_id
    if not panel_id:
        return 

    with SessionLocal() as session:
        panel_images = session.query(PanelImage).order_by(PanelImage.aoi_time.desc())

        if material_name:
            material = session.query(Material).filter(Material.material_name==material_name).first()
            material_code = material.material_code

            panel_images = panel_images.filter(PanelImage.material_code==material_code)

        if lot_name:
            lots = session.query(Lot).filter(Lot.lot_name==lot_name).all()

            lot_ids = []

            for lot in lots:
                lot_ids.append(lot.lot_code)

            panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_ids))

        if panel_id:
            panel_images = panel_images.filter(PanelImage.panel_id==panel_id)

        predict_data = panel_failure_data(panel_images.all(), session, pattern_aoi=True, pattern_ai=True, lot_no=True)

        return predict_data
    

@router.post('/update_mes')
async def upload_mes(item: UpdateMESItem):
    lot_codes = item.lot_codes

    loop.run_in_executor(None, upload_mes_task, lot_codes)

    return {"code": 0, "msg": "等待 mes 更新"}


@router.post('/user-id-top')
async def user_id_top(item: JudementItem):
    
    query_str = item.query_str

    with SessionLocal() as session:
        if not query_str:
            user_id = session.query(PanelImage.user_id).group_by(PanelImage.user_id).order_by(func.count(PanelImage.user_id).desc()).limit(10).all()
        else:
            user_id = session.query(PanelImage.user_id).group_by(PanelImage.user_id).filter(PanelImage.user_id.like("%" + query_str + "%")).order_by(func.count(PanelImage.user_id).desc()).limit(10).all()
        all_user_id = []
        for id in user_id:
            if id[0]:
                all_user_id.append(id[0])
        return {"user_id": all_user_id}
    


@router.post('/failure-code-judegment')
async def failure_code_judegment(item: JudementItem):
    query_str = item.query_str
    with SessionLocal() as session:
        if not query_str:
            failure_code = session.query(JudgmentFailure.failure_code).filter(JudgmentFailure.frequency==1).limit(10).all()
        else:
            failure_code = session.query(JudgmentFailure.failure_code).filter(JudgmentFailure.failure_code.like("%" + query_str + "%")).limit(10).all()
        all_failure_code = []
        for code in failure_code:
            if code[0]:
                all_failure_code.append(code[0])
        return {"failure_code": all_failure_code}


@router.post('/export-csv')
async def export_csv(item: FailureImageItem):
    lot_name = item.lot_name
    lot_code = item.lot_code
    panel_ids = item.panel_ids
    failure_ids = item.failure_ids
    # 综合判定结果
    judgments = item.judgments if item.judgments else []
    end_time = item.end_time
    start_time = item.start_time
    ai_result = item.ai_result
    aoi_result = item.aoi_result
    pjudgment = item.pjudgment
    server_hosts = item.server_hosts

    # aoi生成的 panel 时间
    aoi_start_time = item.aoi_start_time
    aoi_end_time = item.aoi_end_time

    predict_data = await loop.run_in_executor(None, export_csv_task, lot_name, lot_code, panel_ids, failure_ids, judgments, pjudgment, end_time, start_time, ai_result, aoi_start_time, aoi_end_time, aoi_result, server_hosts)


    # 提取CSV列名（使用第一个数据项的键）
    header = list(predict_data[0].keys())
    
    # 创建内存文件对象（使用StringIO处理UTF-8编码）
    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=header)
    
    # 写入CSV内容
    writer.writeheader()
    writer.writerows(predict_data)
    
    # 重置文件指针到开头
    output.seek(0)
    
    # 生成安全的文件名（避免路径遍历）
    timestamp = time.strftime("%Y%m%d%H%M%S", time.localtime())
    safe_filename = f"prediction_{timestamp}.csv"
    
    # 创建流式响应
    return StreamingResponse(
        content=io.BytesIO(output.getvalue().encode('utf-8-sig')),  # BOM头支持Excel中文
        media_type="text/csv; charset=utf-8-sig",
        headers={
            "Content-Disposition": f'attachment; filename="{safe_filename}"',
            "Content-Transfer-Encoding": "binary",
            "Cache-Control": "no-store, no-cache, must-revalidate",
            "Pragma": "no-cache",
            "Expires": "0",
            "X-Content-Type-Options": "nosniff",
            "Content-Security-Policy": "default-src 'none'"
        }
    )


@router.post('/export-csv/start')
async def export_csv_start(item: FailureImageItem):
    lot_name = item.lot_name
    lot_code = item.lot_code
    panel_ids = item.panel_ids
    failure_ids = item.failure_ids
    # 综合判定结果
    judgments = item.judgments if item.judgments else []
    end_time = item.end_time
    start_time = item.start_time
    ai_result = item.ai_result
    aoi_result = item.aoi_result
    pjudgment = item.pjudgment
    server_hosts = item.server_hosts

    # aoi生成的 panel 时间
    aoi_start_time = item.aoi_start_time
    aoi_end_time = item.aoi_end_time

    task_id =str(uuid.uuid4())

    # 放入到后台队列中执行
    enqueue_task(export_csv_task, lot_name, lot_code, panel_ids, failure_ids, judgments, pjudgment, end_time, start_time, ai_result, aoi_start_time, aoi_end_time, aoi_result, server_hosts, task_id)

    # loop.run_in_executor(None, export_csv_task, lot_name, lot_code, panel_ids, failure_ids, judgments, pjudgment, end_time, start_time, ai_result, aoi_start_time, aoi_end_time, aoi_result, server_hosts, task_id)

    return {
        "task_id": task_id,
        "message": "CSV导出任务已启动",
        "download_url": f"/export-download/{task_id}"
    }

    
@router.get("/export-download/{task_id}")
async def export_download(task_id: str):
    csv_path = os.path.join(csv_dir, f"{task_id}.csv")

    if not os.path.exists(csv_path):
        return {
        "code": 1,
        "message": "CSV正在生成，请稍后",
    }

    else:
        return FileResponse(
        csv_path,
        media_type="text/csv",
        headers={
            "Content-Disposition": f"attachment; filename={task_id}.csv",
            "Cache-Control": "no-cache, no-store, must-revalidate",
        }
    )

    